package com.gbiac.jeecgRPT.service.Impl;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.jeecgframework.core.util.DynamicDBUtil;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.gbiac.jeecgRPT.service.WecharService;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import java.text.DecimalFormat;

@Service("WecharService")
@Transactional
public class WecharServiceI  implements WecharService{
	
	/**
	 *  生产数据报表数据源
	 */
	private static final String DBKEY_productiveResource = "PR-DB";
	/**
	 * 查询数据
	 * @param time
	 */
	@Override
	public String queryData1() {
		String sql = "SELECT substr(rq,0,4) as time,SUM(CR+ET+YE) as PCU,SUM(YJ+HW) as HY,SUM(JCN) as JCN FROM TB_CD_DATA WHERE RQ >= 20000000 GROUP BY substr(rq,0,4) ORDER BY time asc";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql);
		for (int i = 0; i < list.size(); i++) {
			double pcu = Double.parseDouble(list.get(i).get("PCU").toString());
			double prevPcu = 0;
			double percentagePcu = 0;
			if(i == 0 ){
				percentagePcu =0;
			}else{
				percentagePcu = 100;
			}
			
			if(i  > 0 ){
				prevPcu= Integer.parseInt(list.get(i-1).get("PCU").toString());
				percentagePcu= (pcu-prevPcu)/prevPcu;
				BigDecimal bg = new BigDecimal(percentagePcu);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentagePcu = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentagePCU", percentagePcu + "%");
			
			
			double HY = Double.parseDouble(list.get(i).get("HY").toString());
			double prevHy = 0;
			double percentageHy =0;
			
			if(i == 0 ){
				percentageHy =0;
			}else{
				percentageHy = 100;
			}
			if(i  > 0 ){
				prevHy= Integer.parseInt(list.get(i-1).get("HY").toString());
				percentageHy= (HY-prevHy)/prevHy;
				BigDecimal bg = new BigDecimal(percentageHy);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageHy = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageHY", percentageHy + "%");
			
			double JCN = Double.parseDouble(list.get(i).get("JCN").toString());
			double prevJCN = 0;
			double percentageJCN =0;
			
			if(i == 0 ){
				percentageJCN =0;
			}else{
				percentageJCN = 100;
			}
			if(i  > 0 ){
				prevJCN= Integer.parseInt(list.get(i-1).get("JCN").toString());
				percentageJCN= (JCN-prevJCN)/prevJCN;
				BigDecimal bg = new BigDecimal(percentageJCN);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageJCN = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageJCN", percentageJCN + "%");
		}
		JSONArray ja = new JSONArray();
		String jsonArray = ja.fromObject(list).toString();
		return jsonArray;
	}
	/**
	 * 查询数据（按进出港分组）
	 * @param time
	 */
	@Override
	public String queryData2IO() {
		String sql= "SELECT SUBSTR(rq,0,4)AS TIME,SUM(DECODE(IO,'I',CR+ET+YE,'O',0))AS PCUI,SUM(DECODE(IO,'O',CR+ET+YE,'I',0))AS PCUO,SUM(DECODE(IO,'I',YJ+HW,'O',0))AS HYI,SUM(DECODE(IO,'O',YJ+HW,'I',0))AS HYO,SUM(DECODE(IO,'I',JCN,'O',0))AS JCNI,SUM(DECODE(IO,'O',JCN,'I',0))AS JCNO FROM TB_CD_DATA WHERE RQ>=20000000 GROUP BY SUBSTR(rq,0,4)ORDER BY TIME ASC";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql);
		for (int i = 0; i < list.size(); i++) {
			double PCUI = Double.parseDouble(list.get(i).get("PCUI").toString());
			double prevPCUI = 0;
			double percentagePCUI =0;
			
			if(i == 0 ){
				percentagePCUI =0;
			}else{
				percentagePCUI = 100;
			}
			if(i  > 0 ){
				prevPCUI= Integer.parseInt(list.get(i-1).get("PCUI").toString());
				percentagePCUI= (PCUI-prevPCUI)/prevPCUI;
				BigDecimal bg = new BigDecimal(percentagePCUI);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentagePCUI = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentagePCUI", percentagePCUI + "%");
			
			double PCUO = Double.parseDouble(list.get(i).get("PCUO").toString());
			double prevPCUO = 0;
			double percentagePCUO =0;
			
			if(i == 0 ){
				percentagePCUO =0;
			}else{
				percentagePCUO = 100;
			}
			if(i  > 0 ){
				prevPCUO= Integer.parseInt(list.get(i-1).get("PCUO").toString());
				percentagePCUO= (PCUO-prevPCUO)/prevPCUO;
				BigDecimal bg = new BigDecimal(percentagePCUO);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentagePCUO = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentagePCUO", percentagePCUO + "%");
			
			
			double HYI = Double.parseDouble(list.get(i).get("HYI").toString());
			double prevHYI = 0;
			double percentageHYI =0;
			
			if(i == 0 ){
				percentageHYI =0;
			}else{
				percentageHYI = 100;
			}
			if(i  > 0 ){
				prevHYI= Integer.parseInt(list.get(i-1).get("HYI").toString());
				percentageHYI= (HYI-prevHYI)/prevHYI;
				BigDecimal bg = new BigDecimal(percentageHYI);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageHYI = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageHYI", percentageHYI + "%");
			
			double HYO = Double.parseDouble(list.get(i).get("HYO").toString());
			double prevHYO = 0;
			double percentageHYO =0;
			
			if(i == 0 ){
				percentageHYO =0;
			}else{
				percentageHYO = 100;
			}
			if(i  > 0 ){
				prevHYO= Integer.parseInt(list.get(i-1).get("HYO").toString());
				percentageHYO= (HYI-prevHYO)/prevHYO;
				BigDecimal bg = new BigDecimal(percentageHYO);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageHYO = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageHYO", percentageHYO + "%");
			
			double JCNI = Double.parseDouble(list.get(i).get("JCNI").toString());
			double prevJCNI = 0;
			double percentageJCNI =0;
			
			if(i == 0 ){
				percentageJCNI =0;
			}else{
				percentageJCNI = 100;
			}
			if(i  > 0 ){
				prevJCNI= Integer.parseInt(list.get(i-1).get("JCNI").toString());
				percentageJCNI= (JCNI-prevJCNI)/prevJCNI;
				BigDecimal bg = new BigDecimal(percentageJCNI);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageJCNI = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageJCNI", percentageJCNI + "%");
			
			double JCNO = Double.parseDouble(list.get(i).get("JCNO").toString());
			double prevJCNO = 0;
			double percentageJCNO =0;
			
			if(i == 0 ){
				percentageJCNO =0;
			}else{
				percentageJCNO = 100;
			}
			if(i  > 0 ){
				prevJCNO= Integer.parseInt(list.get(i-1).get("JCNO").toString());
				percentageJCNO= (JCNO-prevJCNO)/prevJCNO;
				BigDecimal bg = new BigDecimal(percentageJCNO);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageJCNO = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageJCNO", percentageJCNO + "%");
		}
		JSONArray ja = new JSONArray();
		String jsonArray = ja.fromObject(list).toString();
		return jsonArray;
	}
	/**
	 * 查询数据（航线分类）
	 * @param time
	 */
	@Override
	public String queryData2HXFL() {
		String sql= "SELECT substr(rq,0,4) as time,SUM(DECODE(HXFL,'DOM',CR+ET+YE,'REG',CR+ET+YE,'INT',0)) as GNPCU,SUM(DECODE(HXFL,'INT',CR+ET+YE,'DOM',0,'REG',0)) as GJPCU,SUM(DECODE(HXFL,'DOM',YJ+HW,'REG',YJ+HW,'INT',0)) as GNHY,SUM(DECODE(HXFL,'INT',YJ+HW,'DOM',0,'REG',0)) as GJHY,SUM(DECODE(HXFL,'DOM',JCN,'REG',JCN,'INT',0)) as GNJCN,SUM(DECODE(HXFL,'INT',JCN,'DOM',0,'REG',0)) as GJJCN FROM TB_CD_DATA WHERE RQ >= 20090000 GROUP BY substr(rq,0,4) ORDER BY time asc";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql);
		for (int i = 0; i < list.size(); i++) {
			double GNPCU = Double.parseDouble(list.get(i).get("GNPCU").toString());
			double prevGNPCU = 0;
			double percentageGNPCU =0;
			
			if(i == 0 ){
				percentageGNPCU =0;
			}else{
				percentageGNPCU = 100;
			}
			if(i  > 0 ){
				prevGNPCU= Integer.parseInt(list.get(i-1).get("GNPCU").toString());
				percentageGNPCU= (GNPCU-prevGNPCU)/prevGNPCU;
				BigDecimal bg = new BigDecimal(percentageGNPCU);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGNPCU = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGNPCU", percentageGNPCU + "%");
			
			double GJPCU = Double.parseDouble(list.get(i).get("GJPCU").toString());
			double prevGJPCU = 0;
			double percentageGJPCU =0;
			
			if(i == 0 ){
				percentageGJPCU =0;
			}else{
				percentageGJPCU = 100;
			}
			if(i  > 0 ){
				prevGJPCU= Integer.parseInt(list.get(i-1).get("GJPCU").toString());
				percentageGJPCU= (GJPCU-prevGJPCU)/prevGJPCU;
				BigDecimal bg = new BigDecimal(percentageGJPCU);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGJPCU = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGJPCU", percentageGJPCU + "%");
			
			double GNHY = Double.parseDouble(list.get(i).get("GNHY").toString());
			double prevGNHY = 0;
			double percentageGNHY =0;
			
			if(i == 0 ){
				percentageGNHY =0;
			}else{
				percentageGNHY = 100;
			}
			if(i  > 0 ){
				prevGNHY= Integer.parseInt(list.get(i-1).get("GNHY").toString());
				percentageGNHY= (GNHY-prevGNHY)/prevGNHY;
				BigDecimal bg = new BigDecimal(percentageGNHY);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGNHY = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGNHY", percentageGNHY + "%");
			
			
			double GJHY = Double.parseDouble(list.get(i).get("GJHY").toString());
			double prevGJHY = 0;
			double percentageGJHY =0;
			
			if(i == 0 ){
				percentageGJHY =0;
			}else{
				percentageGJHY = 100;
			}
			if(i  > 0 ){
				prevGJHY= Integer.parseInt(list.get(i-1).get("GJHY").toString());
				percentageGJHY= (GJHY-prevGJHY)/prevGJHY;
				BigDecimal bg = new BigDecimal(percentageGJHY);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGJHY = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGJHY", percentageGJHY + "%");
			
			double GNJCN = Double.parseDouble(list.get(i).get("GNJCN").toString());
			double prevGNJCN = 0;
			double percentageGNJCN =0;
			
			if(i == 0 ){
				percentageGNJCN =0;
			}else{
				percentageGNJCN = 100;
			}
			if(i  > 0 ){
				prevGNJCN= Integer.parseInt(list.get(i-1).get("GNJCN").toString());
				percentageGNJCN= (GNJCN-prevGNJCN)/prevGNJCN;
				BigDecimal bg = new BigDecimal(percentageGNJCN);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGNJCN = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGNJCN", percentageGNJCN + "%");
			
			
			double GJJCN = Double.parseDouble(list.get(i).get("GJJCN").toString());
			double prevGJJCN = 0;
			double percentageGJJCN =0;
			
			if(i == 0 ){
				percentageGJJCN =0;
			}else{
				percentageGJJCN = 100;
			}
			if(i  > 0 ){
				prevGJJCN= Integer.parseInt(list.get(i-1).get("GJJCN").toString());
				percentageGJJCN= (GJJCN-prevGJJCN)/prevGJJCN;
				BigDecimal bg = new BigDecimal(percentageGJJCN);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGJJCN = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGJJCN", percentageGJJCN + "%");
		}
		JSONArray ja = new JSONArray();
		String jsonArray = ja.fromObject(list).toString();
		return jsonArray;
	}
	/**
	 * 查询旅客人数（代理人分类）
	 * @param time
	 */
	@Override
	public String queryData2DLR() {
		String sql = "SELECT substr(rq,0,4) as time,SUM(DECODE(DLR,'股份',CR+ET+YE,'南航',0,'联邦快递',0)) as GFPCU,SUM(DECODE(DLR,'股份',0,'南航',CR+ET+YE,'联邦快递',0)) as NHPCU,"
				+ "SUM(DECODE(DLR,'股份',0,'南航',0,'联邦快递',CR+ET+YE)) as LBKDPCU,SUM(DECODE(DLR,'股份',YJ+HW,'南航',0,'联邦快递',0)) as GFHY,"
				+ "SUM(DECODE(DLR,'股份',0,'南航',YJ+HW,'联邦快递',0)) as NHHY,SUM(DECODE(DLR,'股份',0,'南航',0,'联邦快递',YJ+HW)) as "
				+ "LBKDHY,SUM(DECODE(DLR,'股份',JCN,'南航',0,'联邦快递',0)) as GFJCN,SUM(DECODE(DLR,'股份',0,'南航',JCN,'联邦快递',0)) as "
				+ "NHJCN,SUM(DECODE(DLR,'股份',0,'南航',0,'联邦快递',JCN)) as LBKDJCN FROM TB_CD_DATA WHERE RQ >= 20090000 "
				+ "GROUP BY substr(rq,0,4) ORDER BY time asc";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql);
		for (int i = 0; i < list.size(); i++) {
			double GFPCU = Double.parseDouble(list.get(i).get("GFPCU").toString());
			double prevGFPCU = 0;
			double percentageGFPCU =0;
			
			if(i == 0 ){
				percentageGFPCU =0;
			}else{
				percentageGFPCU = 100;
			}
			if(i  > 0 ){
				prevGFPCU= Integer.parseInt(list.get(i-1).get("GFPCU").toString());
				percentageGFPCU= (GFPCU-prevGFPCU)/prevGFPCU;
				BigDecimal bg = new BigDecimal(percentageGFPCU);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGFPCU = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGFPCU", percentageGFPCU + "%");
			
			
			double NHPCU = Double.parseDouble(list.get(i).get("NHPCU").toString());
			double prevNHPCU = 0;
			double percentageNHPCU =0;
			
			if(i == 0 ){
				percentageNHPCU =0;
			}else{
				percentageNHPCU = 100;
			}
			if(i  > 0 ){
				prevNHPCU= Integer.parseInt(list.get(i-1).get("NHPCU").toString());
				percentageNHPCU= (NHPCU-prevNHPCU)/prevNHPCU;
				BigDecimal bg = new BigDecimal(percentageNHPCU);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageNHPCU = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageNHPCU", percentageNHPCU + "%");
			
			
			double LBKDPCU = Double.parseDouble(list.get(i).get("LBKDPCU").toString());
			double prevLBKDPCU = 0;
			double percentageLBKDPCU =0;
			
			if(i == 0 ){
				percentageLBKDPCU =0;
			}else{
				percentageLBKDPCU = 100;
			}
			if(i  > 0 && LBKDPCU >0){
				prevLBKDPCU= Integer.parseInt(list.get(i-1).get("LBKDPCU").toString());
				percentageLBKDPCU= (LBKDPCU-prevLBKDPCU)/prevLBKDPCU;
				BigDecimal bg = new BigDecimal(percentageLBKDPCU);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageLBKDPCU = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageLBKDPCU", percentageLBKDPCU + "%");
			
			double GFHY = Double.parseDouble(list.get(i).get("GFHY").toString());
			double prevGFHY = 0;
			double percentageGFHY =0;
			
			if(i == 0 ){
				percentageGFHY =0;
			}else{
				percentageGFHY = 100;
			}
			if(i  > 0 ){
				prevGFHY= Integer.parseInt(list.get(i-1).get("GFHY").toString());
				percentageGFHY= (GFHY-prevGFHY)/prevGFHY;
				BigDecimal bg = new BigDecimal(percentageGFHY);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGFHY = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGFHY", percentageGFHY + "%");
			
			
			double NHHY = Double.parseDouble(list.get(i).get("NHHY").toString());
			double prevNHHY = 0;
			double percentageNHHY =0;
			
			if(i == 0 ){
				percentageNHHY =0;
			}else{
				percentageNHHY = 100;
			}
			if(i  > 0 ){
				prevNHHY= Integer.parseInt(list.get(i-1).get("NHHY").toString());
				percentageNHHY= (NHHY-prevNHHY)/prevNHHY;
				BigDecimal bg = new BigDecimal(percentageNHHY);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageNHHY = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageNHHY", percentageNHHY + "%");
			double LBKDHY = Double.parseDouble(list.get(i).get("LBKDHY").toString());
			double prevLBKDHY = 0;
			double percentageLBKDHY =0;
			
			if(i == 0 ){
				percentageLBKDHY =0;
			}else{
				percentageLBKDHY = 100;
			}
			if(i  > 0 && LBKDHY >0){
				prevLBKDHY= Integer.parseInt(list.get(i-1).get("LBKDHY").toString());
				percentageLBKDHY= (LBKDHY-prevLBKDHY)/prevLBKDHY;
				BigDecimal bg = new BigDecimal(percentageLBKDHY);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageLBKDHY = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageLBKDHY", percentageLBKDHY + "%");
			
			double GFJCN = Double.parseDouble(list.get(i).get("GFJCN").toString());
			double prevGFJCN = 0;
			double percentageGFJCN =0;
			
			if(i == 0 ){
				percentageGFJCN =0;
			}else{
				percentageGFJCN = 100;
			}
			if(i  > 0 ){
				prevGFJCN= Integer.parseInt(list.get(i-1).get("GFJCN").toString());
				percentageGFJCN= (GFJCN-prevGFJCN)/prevGFJCN;
				BigDecimal bg = new BigDecimal(percentageGFJCN);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageGFJCN = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageGFJCN", percentageGFJCN + "%");
			
			
			double NHJCN = Double.parseDouble(list.get(i).get("NHJCN").toString());
			double prevNHJCN = 0;
			double percentageNHJCN =0;
			
			if(i == 0 ){
				percentageNHJCN =0;
			}else{
				percentageNHJCN = 100;
			}
			if(i  > 0 ){
				prevNHJCN= Integer.parseInt(list.get(i-1).get("NHJCN").toString());
				percentageNHJCN= (NHJCN-prevNHJCN)/prevNHJCN;
				BigDecimal bg = new BigDecimal(percentageNHJCN);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageNHJCN = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageNHJCN", percentageNHJCN + "%");
			
			
			double LBKDJCN = Double.parseDouble(list.get(i).get("LBKDJCN").toString());
			double prevLBKDJCN = 0;
			double percentageLBKDJCN =0;
			if(i == 0 ){
				percentageLBKDJCN =0;
			}else{
				percentageLBKDJCN = 100;
			}
			if(i  > 0 && LBKDJCN >0 ){
				prevLBKDJCN= Integer.parseInt(list.get(i-1).get("LBKDJCN").toString());
				percentageLBKDJCN= (LBKDJCN-prevLBKDJCN)/prevLBKDJCN;
				BigDecimal bg = new BigDecimal(percentageLBKDJCN);  
				bg = bg.multiply(new BigDecimal(Double.valueOf(100)));
				percentageLBKDJCN = bg.setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue(); 
			}
			list.get(i).put("percentageLBKDJCN", percentageLBKDJCN + "%");
		}
		JSONArray ja = new JSONArray();
		String jsonArray = ja.fromObject(list).toString();
		return jsonArray;
	}
	/**
	 * 查询数据(饼状图，代理人分类)
	 * @param time
	 */
	@Override
	public String queryData3() {
		String sql = "SELECT substr(rq,0,4) as time,DLR,SUM(CR+ET+YE) as PCU,SUM(YJ+HW) AS HY,SUM(JCN) AS JCN FROM TB_CD_DATA WHERE RQ >= 20000000 GROUP BY substr(rq,0,4),DLR ORDER BY time asc";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql);
//		JSONObject result = new JSONObject();
//		for (int i = 0; i <  list.size(); i++) {
//			if (!result.containsKey( list.get(i).get("TIME").toString())) {
//				result.put(list.get(i).get("TIME"), new JSONArray());
//			}
//			JSONArray yearArr = result.getJSONArray(list.get(i).get("TIME").toString());
//			yearArr.add(list.get(i));
//		}
		Map<String, JSONArray> map = new HashMap<String, JSONArray>();
		JSONArray result = new JSONArray();
		for (int i = 0; i < list.size(); i++) {
			if (!map.containsKey( list.get(i).get("TIME").toString())) {
				map.put(list.get(i).get("TIME").toString(), new JSONArray());
			}
			JSONArray yearArr = map.get(list.get(i).get("TIME").toString());
			yearArr.add(list.get(i));
			map.put(list.get(i).get("TIME").toString(), yearArr);
		}
		result.addAll(map.values());
		return result.toString();
	}
	/**
	 * 查询旅客人数(饼状图，航线分类)
	 * @param time
	 */
	@Override
	public String queryData4() {
		String sql= "SELECT substr(rq,0,4) as time,SUM(DECODE(HXFL,'DOM',CR+ET+YE,'REG',CR+ET+YE,'INT',0)) as GNPCU,"
				+ "SUM(DECODE(HXFL,'INT',CR+ET+YE,'DOM',0,'REG',0)) as GJPCU,SUM(DECODE(HXFL,'DOM',YJ+HW,'REG',YJ+HW,'INT',0)) as GNHY,"
				+ "SUM(DECODE(HXFL,'INT',YJ+HW,'DOM',0,'REG',0)) as GJHY,SUM(DECODE(HXFL,'DOM',JCN,'REG',JCN,'INT',0)) as GNJCN,"
				+ "SUM(DECODE(HXFL,'INT',JCN,'DOM',0,'REG',0)) as GJJCN FROM TB_CD_DATA WHERE RQ >= 20090000 GROUP BY substr(rq,0,4) ORDER BY time asc";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql);
		JSONArray ja = new JSONArray();
		String jsonArray = ja.fromObject(list).toString();
		return jsonArray.toString();
	}
	@Override
	public String airportData(String rq) {
		String sqlbnidata = "SELECT bn ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) < 7 then concat(substr(rq,0,4),'01') when substr(rq,5,2) >= 7 then concat(substr(rq,0,4),'07') end as bn  from TB_CD_DATA t) WHERE RQ >= 20000000 and io='I' GROUP BY DLR,bn ORDER BY bn asc";
		String sqlbnodata = "SELECT bn ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) < 7 then concat(substr(rq,0,4),'01') when substr(rq,5,2) >= 7 then concat(substr(rq,0,4),'07') end as bn  from TB_CD_DATA t) WHERE RQ >= 20000000 and io='O' GROUP BY DLR,bn ORDER BY bn asc";
		String sqlbngndata = "SELECT bn ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) < 7 then concat(substr(rq,0,4),'01') when substr(rq,5,2) >= 7 then concat(substr(rq,0,4),'07') end as bn  from TB_CD_DATA t) WHERE RQ >= 20000000 and hxfl='DOM' GROUP BY DLR,bn ORDER BY bn asc";
		String sqlbngjdata = "SELECT bn ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) < 7 then concat(substr(rq,0,4),'01') when substr(rq,5,2) >= 7 then concat(substr(rq,0,4),'07') end as bn  from TB_CD_DATA t) WHERE RQ >= 20000000 and hxfl='INT' GROUP BY DLR,bn ORDER BY bn asc";
		
		String sqljdidata = "SELECT jidu ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) between 1 and 3 then concat(substr(rq,0,4),'01') when substr(rq,5,2) between 4 and 6 then concat(substr(rq,0,4),'02') when substr(rq,5,2) between 7 and 9 then concat(substr(rq,0,4),'03') when substr(rq,5,2) between 10 and 12 then concat(substr(rq,0,4),'04') end as jidu  from TB_CD_DATA t) WHERE RQ >= 20000000 and io='I' GROUP BY DLR,jidu ORDER BY jidu asc" ;
		String sqljdodata = "SELECT jidu ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) between 1 and 3 then concat(substr(rq,0,4),'01') when substr(rq,5,2) between 4 and 6 then concat(substr(rq,0,4),'02') when substr(rq,5,2) between 7 and 9 then concat(substr(rq,0,4),'03') when substr(rq,5,2) between 10 and 12 then concat(substr(rq,0,4),'04') end as jidu  from TB_CD_DATA t) WHERE RQ >= 20000000 and io='O' GROUP BY DLR,jidu ORDER BY jidu asc";
		String sqljdgndata = "SELECT jidu ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) between 1 and 3 then concat(substr(rq,0,4),'01') when substr(rq,5,2) between 4 and 6 then concat(substr(rq,0,4),'02') when substr(rq,5,2) between 7 and 9 then concat(substr(rq,0,4),'03') when substr(rq,5,2) between 10 and 12 then concat(substr(rq,0,4),'04') end as jidu  from TB_CD_DATA t) WHERE RQ >= 20000000 and hxfl='DOM' GROUP BY DLR,jidu ORDER BY jidu asc";
		String sqljdgjdata = "SELECT jidu ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,SUM(jcn) as bnijc FROM (select t.*,case when substr(rq,5,2) between 1 and 3 then concat(substr(rq,0,4),'01') when substr(rq,5,2) between 4 and 6 then concat(substr(rq,0,4),'02') when substr(rq,5,2) between 7 and 9 then concat(substr(rq,0,4),'03') when substr(rq,5,2) between 10 and 12 then concat(substr(rq,0,4),'04') end as jidu  from TB_CD_DATA t) WHERE RQ >= 20000000 and hxfl='INT' GROUP BY DLR,jidu ORDER BY jidu asc";
		
		String sqlyidata  = "select DLR,sum(case RQ when '201607' then bnihw else 0 end) as bnihw1,"
				+ "sum(case RQ when '201608' then bnihw else 0 end) as bnihw2,"
				+ "sum(case RQ when '201607' then bnilk else 0 end) as bnilk1,"
				+ "sum(case RQ when '201608' then bnilk else 0 end) as bnilk2,"
				+ "sum(case RQ when '201607' then bnijc else 0 end) as bnijc1,"
				+ "sum(case RQ when '201608' then bnijc else 0 end) as bnijc2 from "
				+ "(SELECT substr(RQ,0,6) as rq ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,"
				+ "SUM(jcn) as bnijc FROM TB_CD_DATAWHERE substr(RQ,0,6) "
				+ "between 201607 and 201608 and io='I' GROUP BY substr(RQ,0,6),DLR) group by DLR";
		String sqlyodata  = "select DLR,sum(case RQ when '201607' then bnihw else 0 end) as bnihw1,"
				+ "sum(case RQ when '201608' then bnihw else 0 end) as bnihw2,"
				+ "sum(case RQ when '201607' then bnilk else 0 end) as bnilk1,"
				+ "sum(case RQ when '201608' then bnilk else 0 end) as bnilk2,"
				+ "sum(case RQ when '201607' then bnijc else 0 end) as bnijc1,"
				+ "sum(case RQ when '201608' then bnijc else 0 end) as bnijc2 from "
				+ "(SELECT substr(RQ,0,6) as rq ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,"
				+ "SUM(jcn) as bnijc FROM TB_CD_DATAWHERE substr(RQ,0,6) "
				+ "between 201607 and 201608 and io='O' GROUP BY substr(RQ,0,6),DLR) group by DLR";
		String sqlygndata = "select DLR,sum(case RQ when '201607' then bnihw else 0 end) as bnihw1,"
				+ "sum(case RQ when '201608' then bnihw else 0 end) as bnihw2,"
				+ "sum(case RQ when '201607' then bnilk else 0 end) as bnilk1,"
				+ "sum(case RQ when '201608' then bnilk else 0 end) as bnilk2,"
				+ "sum(case RQ when '201607' then bnijc else 0 end) as bnijc1,"
				+ "sum(case RQ when '201608' then bnijc else 0 end) as bnijc2 from "
				+ "(SELECT substr(RQ,0,6) as rq ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,"
				+ "SUM(jcn) as bnijc FROM TB_CD_DATAWHERE substr(RQ,0,6) "
				+ "between 201607 and 201608 and hxfl='DOM' GROUP BY substr(RQ,0,6),DLR) group by DLR";
		String sqlygjdata = "select DLR,sum(case RQ when '201607' then bnihw else 0 end) as bnihw1,"
				+ "sum(case RQ when '201608' then bnihw else 0 end) as bnihw2,"
				+ "sum(case RQ when '201607' then bnilk else 0 end) as bnilk1,"
				+ "sum(case RQ when '201608' then bnilk else 0 end) as bnilk2,"
				+ "sum(case RQ when '201607' then bnijc else 0 end) as bnijc1,"
				+ "sum(case RQ when '201608' then bnijc else 0 end) as bnijc2 from "
				+ "(SELECT substr(RQ,0,6) as rq ,DLR,SUM(hw)+SUM(yj) as bnihw,SUM(et)+SUM(cr) as bnilk,"
				+ "SUM(jcn) as bnijc FROM TB_CD_DATAWHERE substr(RQ,0,6) "
				+ "between 201607 and 201608 and hxfl='INT' GROUP BY substr(RQ,0,6),DLR) group by DLR";
				
				
		List<Map<String, Object>> listbnidata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlbnidata);
		List<Map<String, Object>> listbnodata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlbnodata);
		List<Map<String, Object>> listbngndata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlbngndata);
		List<Map<String, Object>> listbngjdata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlbngjdata);
		List<Map<String, Object>> listjdidata = DynamicDBUtil.findList(DBKEY_productiveResource, sqljdidata);
		List<Map<String, Object>> listjdodata = DynamicDBUtil.findList(DBKEY_productiveResource, sqljdodata);
		List<Map<String, Object>> listjdgndata = DynamicDBUtil.findList(DBKEY_productiveResource, sqljdgndata);
		List<Map<String, Object>> listjdgjdata = DynamicDBUtil.findList(DBKEY_productiveResource, sqljdgjdata);
		List<Map<String, Object>> listyidata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlyidata);
		List<Map<String, Object>> listyodata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlyodata);
		List<Map<String, Object>> listygndata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlygndata);
		List<Map<String, Object>> lisygjdata = DynamicDBUtil.findList(DBKEY_productiveResource, sqlygjdata);
		JSONArray ijsonArray = new JSONArray();
		ijsonArray.add(0,listbnidata);
		ijsonArray.add(1,listjdidata);
		ijsonArray.add(2,listyidata);
		JSONArray ojsonArray = new JSONArray();
		ojsonArray.add(0,listbnodata);
		ojsonArray.add(1,listjdodata);
		ojsonArray.add(2,listyodata);
		JSONArray gnjsonArray = new JSONArray();
		gnjsonArray.add(0,listbngndata);
		gnjsonArray.add(1,listjdgndata);
		gnjsonArray.add(2,listygndata);
		JSONArray gjjsonArray = new JSONArray();
		gjjsonArray.add(0,listbngjdata);
		gjjsonArray.add(1,listjdgjdata);
		gjjsonArray.add(2,lisygjdata);
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("idata", ijsonArray);
		jsonObject.put("odata", ojsonArray);
		jsonObject.put("gndata", gnjsonArray);
		jsonObject.put("gjdata", gjjsonArray);
		return jsonObject.toString();
	}
	@Override
	public String dayAirportData(String rq) {
		String sql= "SELECT ? as time ,SUM(d.hw)+SUM(d.yj) as HY,SUM(d.et)+SUM(d.cr) as PCU,SUM(d.jcn) as JCN FROM TB_CD_DATA d  WHERE rq = ?";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql,rq,rq);
		JSONArray ja = new JSONArray();
		String jsonArray = ja.fromObject(list).toString();
		return jsonArray;
	
	}
	@Override
	public String hotAirportData(String rq) {
		String sqlI = "SELECT ? as time ,SUM(t.hw)+SUM(t.yj) AS ihw,SUM(t.jcn) AS ijcn,sum(t.cr)+SUM(t.et) AS ipcu,substr(t.qjsj,0,2) as ihour FROM TB_CD_DATA t where rq =? and io='I'  GROUP BY substr(t.qjsj,0,2) ORDER BY substr(t.qjsj,0,2) asc";
		String sqlO = "SELECT ? as time ,SUM(t.hw)+SUM(t.yj) AS ohw,SUM(t.jcn) AS ojcn,sum(t.cr)+SUM(t.et) AS opcu,substr(t.qjsj,0,2) as ohour FROM TB_CD_DATA t where rq =? and io='O'  GROUP BY substr(t.qjsj,0,2) ORDER BY substr(t.qjsj,0,2) asc";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sqlI,rq,rq);
		list.addAll(DynamicDBUtil.findList(DBKEY_productiveResource, sqlO,rq,rq));
		JSONArray ja = new JSONArray();
		ja.add(sqlO);
		String jsonString = ja.fromObject(list).toString();
		return jsonString;
	}
	@Override
	public String airportReportData(String rq) {
		DecimalFormat df = new DecimalFormat("###.0");
		String sql = "SELECT  ? AS time, SUM(t.hw)+SUM(t.yj) AS hw,SUM(t.hw) AS hwhy,SUM(t.yj) AS yjhy,SUM(t.jcn) AS jcn,sum(t.cr)+SUM(t.et) AS pcu FROM TB_CD_DATA t where substr(t.rq,0,6) =substr(?,0,6)";
		String sqlqn ="SELECT ? AS time, SUM(t.hw)+SUM(t.yj) as qnhw,SUM(t.hw) as qnhwhy,SUM(t.yj) as qnyjhy,SUM(t.et)+SUM(t.cr) as qnpcu,SUM(t.jcn) as qnjcn  FROM TB_CD_DATA t  WHERE substr(t.rq,0,6) = substr(TO_CHAR(add_months(TO_DATE(?,'yyyymmdd'),-12),'yyyymmdd'),0,6) ";
		String sqli = "SELECT  ? AS time, SUM(t.hw)+SUM(t.yj) AS ihw,SUM(t.hw) AS ihwhy,SUM(t.yj) AS iyjhy,SUM(t.jcn) AS ijcn,sum(t.cr)+SUM(t.et) AS ipcu FROM TB_CD_DATA t where substr(t.rq,0,6) =substr(?,0,6) and io='I'";
		String sqliqn ="SELECT ? AS time, SUM(t.hw)+SUM(t.yj) as iqnhw,SUM(t.hw) as iqnhwhy,SUM(t.yj) as iqnyjhy,SUM(t.et)+SUM(t.cr) as iqnpcu,SUM(t.jcn) as iqnjcn  FROM TB_CD_DATA t  WHERE substr(t.rq,0,6) = substr(TO_CHAR(add_months(TO_DATE(?,'yyyymmdd'),-12),'yyyymmdd'),0,6) and io='I'";
		String sqlo = "SELECT  ? AS time, SUM(t.hw)+SUM(t.yj) AS ohw,SUM(t.hw) AS ohwhy,SUM(t.yj) AS oyjhy,SUM(t.jcn) AS ojcn,sum(t.cr)+SUM(t.et) AS opcu FROM TB_CD_DATA t where substr(t.rq,0,6) =substr(?,0,6) and io='O'";
		String sqloqn ="SELECT ? AS time, SUM(t.hw)+SUM(t.yj) as oqnhw,SUM(t.hw) as oqnhwhy,SUM(t.yj) as oqnyjhy,SUM(t.et)+SUM(t.cr) as oqnpcu,SUM(t.jcn) as oqnjcn  FROM TB_CD_DATA t  WHERE substr(t.rq,0,6) = substr(TO_CHAR(add_months(TO_DATE(?,'yyyymmdd'),-12),'yyyymmdd'),0,6) and io='O'";
		List<Map<String, Object>> list = DynamicDBUtil.findList(DBKEY_productiveResource, sql,rq,rq);
		list.addAll(DynamicDBUtil.findList(DBKEY_productiveResource, sqlqn,rq,rq));
		double hwhyzzl= Double.parseDouble(list.get(0).get("HWHY").toString())/Double.parseDouble(list.get(1).get("QNHWHY").toString())*100;
		double pcuzzl =Double.parseDouble(list.get(0).get("PCU").toString())/Double.parseDouble(list.get(1).get("QNPCU").toString())*100;
		double jcnzzl = Double.parseDouble(list.get(0).get("JCN").toString())/Double.parseDouble(list.get(1).get("QNJCN").toString())*100;
		double yjhyzzl = Double.parseDouble(list.get(0).get("YJHY").toString())/Double.parseDouble(list.get(1).get("QNYJHY").toString())*100;
		double hwzzl =  Double.parseDouble(list.get(0).get("HW").toString())/Double.parseDouble(list.get(1).get("QNHW").toString())*100;
		JSONObject jsonObject = new JSONObject();
		jsonObject.put("HWHY", df.format(hwhyzzl));
		jsonObject.put("PCU", df.format(pcuzzl));
		jsonObject.put("JCN", df.format(jcnzzl));
		jsonObject.put("YJHY", df.format(yjhyzzl));
		jsonObject.put("HW", df.format(hwzzl));
		list.add(jsonObject);
		List<Map<String, Object>> list1 = DynamicDBUtil.findList(DBKEY_productiveResource, sqli,rq,rq);
		list1.addAll(DynamicDBUtil.findList(DBKEY_productiveResource, sqliqn,rq,rq));
		double ihwhyzzl= Double.parseDouble(list1.get(0).get("IHWHY").toString())/Double.parseDouble(list1.get(1).get("IQNHWHY").toString())*100;
		double ipcuzzl =Double.parseDouble(list1.get(0).get("IPCU").toString())/Double.parseDouble(list1.get(1).get("IQNPCU").toString())*100;
		double ijcnzzl = Double.parseDouble(list1.get(0).get("IJCN").toString())/Double.parseDouble(list1.get(1).get("IQNJCN").toString())*100;
		double iyjhyzzl = Double.parseDouble(list1.get(0).get("IYJHY").toString())/Double.parseDouble(list1.get(1).get("IQNYJHY").toString())*100;
		double ihwzzl = Double.parseDouble(list1.get(0).get("IHW").toString())/Double.parseDouble(list1.get(1).get("IQNHW").toString())*100;
		JSONObject jsonObjectI = new JSONObject();
		jsonObjectI.put("IHWHY", df.format(ihwhyzzl));
		jsonObjectI.put("IPCU", df.format(ipcuzzl));
		jsonObjectI.put("IJCN", df.format(ijcnzzl));
		jsonObjectI.put("IYJHY", df.format(iyjhyzzl));
		jsonObjectI.put("IHW", df.format(ihwzzl));
		list1.add(jsonObjectI);
		List<Map<String, Object>> list2 = DynamicDBUtil.findList(DBKEY_productiveResource, sqlo,rq,rq);
		list2.addAll(DynamicDBUtil.findList(DBKEY_productiveResource, sqloqn,rq,rq));
		double ohwhyzzl= Double.parseDouble(list2.get(0).get("OHWHY").toString())/Double.parseDouble(list2.get(1).get("OQNHWHY").toString())*100;
		double opcuzzl =Double.parseDouble(list2.get(0).get("OPCU").toString())/Double.parseDouble(list2.get(1).get("OQNPCU").toString())*100;
		double ojcnzzl = Double.parseDouble(list2.get(0).get("OJCN").toString())/Double.parseDouble(list2.get(1).get("OQNJCN").toString())*100;
		double oyjhyzzl = Double.parseDouble(list2.get(0).get("OYJHY").toString())/Double.parseDouble(list2.get(1).get("OQNYJHY").toString())*100;
		double ohwzzl = Double.parseDouble(list2.get(0).get("OHW").toString())/Double.parseDouble(list2.get(1).get("OQNHW").toString())*100;
		JSONObject jsonObjectO = new JSONObject();
		jsonObjectO.put("OHWHY", df.format(ohwhyzzl));
		jsonObjectO.put("OPCU", df.format(opcuzzl));
		jsonObjectO.put("OJCN", df.format(ojcnzzl));
		jsonObjectO.put("OYJHY", df.format(oyjhyzzl));
		jsonObjectO.put("OHW", df.format(ohwzzl));
		list2.add(jsonObjectO);
		JSONArray jsonArray = new JSONArray();
		jsonArray.add(jsonArray.fromObject(list));
		jsonArray.add(jsonArray.fromObject(list1));
		jsonArray.add(jsonArray.fromObject(list2));
		String jsonString = jsonArray.toString();
		return jsonString;
	}
}
